package br.com.sisgerenciamento.dao.locacao;

import java.util.ArrayList;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.AliasToBeanResultTransformer;

import br.com.sisgerenciamento.dto.locacao.LocacaoDTO;
import br.com.sisgerenciamento.exceptions.AlterarException;
import br.com.sisgerenciamento.exceptions.ApplicationException;
import br.com.sisgerenciamento.exceptions.InserirException;
import br.com.sisgerenciamento.generic.GenericDAO;
import br.com.sisgerenciamento.model.locacao.LocacaoVO;
import br.com.sisgerenciamento.model.locacao.StatusLocacaoEnum;


public class LocacaoDAOServiceImpl extends GenericDAO<LocacaoVO> implements LocacaoDAOService{

	public LocacaoVO inserir(LocacaoVO vo) throws InserirException {
		return (LocacaoVO) insert(vo);
	}

	public void alterar(LocacaoVO vo) throws AlterarException {
		update(vo);
	}

	public LocacaoVO consultarPorCodigo(Long codigo)throws ApplicationException {
		Session session = null;
		try {
			session = getSessionAberta();
			Criteria criteria = session.createCriteria(LocacaoVO.class);
			criteria.add(Restrictions.eq("codigo", codigo));
			LocacaoVO vo = (LocacaoVO) consultar(criteria);
			return vo;
		} catch (Exception e) {
			throw new ApplicationException(e.getMessage());
		} finally {
			if (session != null)
				session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public ArrayList<LocacaoDTO> consultarLocacaoPorNomeCliente(String nomeCliente) throws ApplicationException {
		Session session = null;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT loc.codigo AS codigo, ");
		sql.append("loc.descricao AS descricao, ");
		sql.append("loc.status AS status, ");
		sql.append("loc.dataHoraEntrega AS dataHoraEntrega, ");
		sql.append("p.codigo AS codigoPedido, ");
		sql.append("c.nome AS nomeCliente, ");
		sql.append("loc.usuario AS codigoUsuario ");
		sql.append("FROM locacao loc ");
		sql.append("INNER JOIN pedido p ON (p.locacao_id = loc.codigo) ");
		sql.append("INNER JOIN cliente c ON (c.codigo = p.cliente_codigo)");
		
		if(nomeCliente != null && !nomeCliente.equals("")){
			sql.append(" WHERE c.nome LIKE '%"+nomeCliente+"%'");
		}
		
		try {
			session = getSessionAberta();
			SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
			sqlQuery.addScalar("codigo", Hibernate.LONG);
			sqlQuery.addScalar("descricao", Hibernate.STRING);
			sqlQuery.addScalar("status", Hibernate.INTEGER);
			sqlQuery.addScalar("dataHoraEntrega", Hibernate.TIMESTAMP);
			sqlQuery.addScalar("codigoPedido", Hibernate.LONG);
			sqlQuery.addScalar("nomeCliente", Hibernate.STRING);
			sqlQuery.addScalar("codigoUsuario", Hibernate.LONG);
			
			
			sqlQuery.setResultTransformer(new AliasToBeanResultTransformer(LocacaoDTO.class));
			return (ArrayList<LocacaoDTO>) sqlQuery.list();
		} catch (Exception e) {
			throw new ApplicationException(e.getMessage());
		} finally {
			if (session != null)
				session.close();
		}
	}

	public void excluir(LocacaoVO vo) throws AlterarException {
		delete(vo);
	}

	@SuppressWarnings("unchecked")
	public ArrayList<LocacaoDTO> consultarLocacaoPorPedido(Long codigoPedido)throws ApplicationException {
		Session session = null;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT loc.codigo AS codigo, ");
		sql.append("loc.descricao AS descricao, ");
		sql.append("loc.status AS status, ");
		sql.append("loc.dataHoraEntrega AS dataHoraEntrega, ");
		sql.append("p.codigo AS codigoPedido, ");
		sql.append("c.nome AS nomeCliente, ");
		sql.append("loc.usuario AS codigoUsuario ");
		sql.append("FROM locacao loc ");
		sql.append("INNER JOIN pedido p ON (p.locacao_id = loc.codigo) ");
		sql.append("INNER JOIN cliente c ON (c.codigo = p.cliente_codigo)");
		
		if(codigoPedido != null){
			sql.append(" WHERE p.codigo = :codigoPedido");
		}
		
		try {
			session = getSessionAberta();
			SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
			sqlQuery.addScalar("codigo", Hibernate.LONG);
			sqlQuery.addScalar("descricao", Hibernate.STRING);
			sqlQuery.addScalar("status", Hibernate.INTEGER);
			sqlQuery.addScalar("dataHoraEntrega", Hibernate.TIMESTAMP);
			sqlQuery.addScalar("codigoPedido", Hibernate.LONG);
			sqlQuery.addScalar("nomeCliente", Hibernate.STRING);
			sqlQuery.addScalar("codigoUsuario", Hibernate.LONG);
			
			if(codigoPedido != null){
				sqlQuery.setLong("codigoPedido", codigoPedido);
			}
			
			sqlQuery.setResultTransformer(new AliasToBeanResultTransformer(LocacaoDTO.class));
			return (ArrayList<LocacaoDTO>) sqlQuery.list();
		} catch (Exception e) {
			throw new ApplicationException(e.getMessage());
		} finally {
			if (session != null)
				session.close();
		}
	}
	
	@SuppressWarnings("unchecked")
	public ArrayList<LocacaoDTO> buscarLocacaoPendente()throws ApplicationException {
		Session session = null;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT loc.codigo AS codigo, ");
		sql.append("loc.descricao AS descricao, ");
		sql.append("loc.status AS status, ");
		sql.append("loc.dataHoraEntrega AS dataHoraEntrega, ");
		sql.append("p.codigo AS codigoPedido, ");
		sql.append("c.nome AS nomeCliente, ");
		sql.append("loc.usuario AS codigoUsuario ");
		sql.append("FROM locacao loc ");
		sql.append("INNER JOIN pedido p ON (p.locacao_id = loc.codigo) ");
		sql.append("INNER JOIN cliente c ON (c.codigo = p.cliente_codigo)");
		
		sql.append(" WHERE loc.status = :status");
		
		try {
			session = getSessionAberta();
			SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
			sqlQuery.addScalar("codigo", Hibernate.LONG);
			sqlQuery.addScalar("descricao", Hibernate.STRING);
			sqlQuery.addScalar("status", Hibernate.INTEGER);
			sqlQuery.addScalar("dataHoraEntrega", Hibernate.TIMESTAMP);
			sqlQuery.addScalar("codigoPedido", Hibernate.LONG);
			sqlQuery.addScalar("nomeCliente", Hibernate.STRING);
			sqlQuery.addScalar("codigoUsuario", Hibernate.LONG);
			
			sqlQuery.setInteger("status", StatusLocacaoEnum.LOCADO.getValor());
			
			
			sqlQuery.setResultTransformer(new AliasToBeanResultTransformer(LocacaoDTO.class));
			return (ArrayList<LocacaoDTO>) sqlQuery.list();
		} catch (Exception e) {
			throw new ApplicationException(e.getMessage());
		} finally {
			if (session != null)
				session.close();
		}
	}
}
